--    Author    : ChenErHao
--    Name      : EDW.EV_LOAF_TRAN.HQL
--    Functions : 
--    Purpose   : Daily saving the variation data from ods
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-05-03  ChenErHao           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE EDW.EV_LOAF_TRAN PARTITION (DATA_DATE = '#V_DATA_DATE#' ,DATA_SRC_ORG = '#V_DATA_SRC_ORG#')
SELECT 
    FIN_ORG_NO                               -- 金融机构编码
    ,CLIENT_TYPE                             -- 客户类型
    ,CLIENT_NO                               -- 借款人代码
    ,CLIENT_INDUSTRY                         -- 贷款主体行业类别
    ,CLIENT_DIST                             -- 借款人所在地区
    ,ECONOMY_TYPE                            -- 企业出资人经济成分
    ,ENTERPRISE_SCALE                        -- 企业规模
    ,DUEBILL_NO                              -- 贷款借据编码
    ,BUSINESS_TYPE                           -- 贷款产品类别
    ,LOAN_INDUSTRY                           -- 贷款实际投向
    ,DRAWDOWN_DATE                           -- 贷款发放日期
    ,MATURITY_DATE                           -- 贷款到期日期
    ,ACTUAL_END_DATE                         -- 贷款实际终止日期
    ,CCY                                     -- 贷款币种
    ,CASE WHEN LOAF.DATA_DATE = '#V_DATA_DATE#' THEN NVL(OCCUR_AMOUNT,0) ELSE 0 END AS OCCUR_AMOUNT -- 贷款发生金额
    ,IF_FLOAT                                -- 利率是否固定
    ,RATE                                    -- 利率水平
    ,GUARANTY_TYPE                           -- 贷款担保方式
    ,LOAN_STATUS                             -- 贷款状态
    ,DRAWDOWN_OR_REPAY                       -- 贷款发放收回标志
    ,CASE
        WHEN LOAF.LOAN_STATUS <>  'FS01' THEN '7'
        WHEN LOAF.DRAWDOWN_DATE IS NULL OR LOAF.DRAWDOWN_DATE='' OR  LOAF.MATURITY_DATE IS NULL OR LOAF.MATURITY_DATE='' THEN '7'		
        WHEN F_MONTHDIFF(LOAF.DRAWDOWN_DATE,LOAF.MATURITY_DATE) <=6  THEN '1'
        WHEN F_MONTHDIFF(LOAF.DRAWDOWN_DATE,LOAF.MATURITY_DATE) <=12  THEN '2'
        WHEN F_MONTHDIFF(LOAF.DRAWDOWN_DATE,LOAF.MATURITY_DATE) <=36  THEN '3'
        WHEN F_MONTHDIFF(LOAF.DRAWDOWN_DATE,LOAF.MATURITY_DATE) <=60  THEN '4'
        WHEN F_MONTHDIFF(LOAF.DRAWDOWN_DATE,LOAF.MATURITY_DATE) <=120 THEN '5'
        WHEN F_MONTHDIFF(LOAF.DRAWDOWN_DATE,LOAF.MATURITY_DATE) >120  THEN '6'
        ELSE '7'
     END AS TERM_CODE                     -- 贷款期限
    ,CASE WHEN LOAF.DATA_DATE = '#V_DATA_DATE#' THEN NVL(OCCUR_AMOUNT,0)*NVL(RATE,0) ELSE 0 END AS WSUM_AMOUNT -- 发生额加权值
    ,ORG.ORG_CODE_2 AS ORG_TYPE           -- 机构类型
    ,CASE
        WHEN LOAF.LOAN_STATUS <>  'FS01' THEN 'Z'
        WHEN LOAF.DRAWDOWN_DATE IS NULL OR LOAF.DRAWDOWN_DATE='' OR  LOAF.MATURITY_DATE IS NULL OR LOAF.MATURITY_DATE='' THEN 'Z'
        WHEN F_MONTHDIFF(LOAF.DRAWDOWN_DATE,LOAF.MATURITY_DATE) <=12  THEN 'A'
        WHEN F_MONTHDIFF(LOAF.DRAWDOWN_DATE,LOAF.MATURITY_DATE) >12  THEN 'B'
        ELSE 'Z'
     END AS TERM_TYPE                     -- 期限类型
FROM ODS.SUST_DLMG_LOAF LOAF 
LEFT JOIN DIMENSION.T_ORG_BIZ_LVL ORG ON(LOAF.FIN_ORG_NO=ORG.ORG_CODE_4)
WHERE LOAF.DATA_DATE = '#V_DATA_DATE#' AND LOAF.DATA_SRC_ORG = '#V_DATA_SRC_ORG#'
;